
*****ESTIMATE ILLIQUIDITY*****
use PERMNO date RET PRC VOL DLRET DLSTCD using "$jfqa_rep/crsp_daily_all.dta", clear

qui{
rename PERMNO permno
rename RET ret
rename PRC prc
rename VOL vol
rename DLRET dlret
rename DLSTCD dlstcd

*define dates
gen month=month(date)
gen year=year(date)
gen monthyear=ym(year, month)

*delete duplicates and clean prc, ret
drop if date==date[_n-1] & permno==permno[_n-1]
drop date

replace prc=abs(prc)

*de-listed returns
replace ret=dlret if missing(ret)
replace ret=-0.3 if missing(ret) & dlstcd==500
replace ret=-0.3 if missing(ret) & dlstcd==520
replace ret=-0.3 if missing(ret) & dlstcd>550 & dlstcd<575
replace ret=-0.3 if missing(ret) & dlstcd==580
replace ret=-0.3 if missing(ret) & dlstcd==584
replace ret=-1 if missing(ret) & ~missing(dlstcd)
drop dlstcd dlret

*estimate illiquidity
gen illiquid=(abs(ret)/(vol*prc))*1000000

drop if missing(illiquid)
drop if abs(prc)<5

collapse illiquid, by(permno year month monthyear)

}

save "$jfqa_rep/T7_Amihud.dta", replace


******DETREND ILLIQUIDITY******
use PERMCO PERMNO TICKER NCUSIP RET RETX date PRC SHROUT DLRET DLRETX DLSTCD DISTCD EXCHCD SHRCD VOL BID ASK CFACPR using "$jfqa_rep/crsp_monthly_all.dta", clear 

qui{
rename PERMCO permco
rename PERMNO permno
rename TICKER ticker
rename NCUSIP ncusip
rename RET ret
rename RETX retx
rename PRC prc
rename SHROUT shrout
rename DLRET dlret
rename DLRETX dlretx
rename DLSTCD dlstcd
rename DISTCD distcd
rename EXCHCD exchcd
rename SHRCD shrcd
rename VOL vol
rename BID bid 
rename ASK ask
rename CFACPR cfacpr

*define dates and mktcap
gen year=year(date)
gen month=month(date)
gen monthyear=ym(year, month)

*delete duplicates and clean prc
sort permno date
drop if date==date[_n-1] & permno==permno[_n-1]
replace prc=abs(prc)

*clean data
sort permno monthyear
tsset permno monthyear
drop if prc<5
drop if missing(F.monthyear)
drop if missing(prc*shrout)

*merge illiquidity 
merge 1:1 permno month year monthyear using "$jfqa_rep/T7_Amihud.dta"
keep if _merge==3
drop _merge

rm "$jfqa_rep/T7_Amihud.dta"

*drop outliers
bysort monthyear: egen p1 = pctile(illiquid), p(1)
bysort monthyear: egen p99 = pctile(illiquid), p(99)
keep if inrange(illiquid, p1, p99)

*monthly illiquidity
collapse illiquid, by(year month monthyear)

tsset monthyear

gen illiquid_12=(L.ill+L2.ill+L3.ill+L4.ill+L5.ill+L6.ill+L7.ill+L8.ill+L9.ill+L10.ill+L11.ill+L12.ill)/12
	
gen illiquid_detrend=illiquid-illiquid_12

keep illiquid_detrend year month
}

save "$jfqa_rep/T7_Amihud_Detrend.dta", replace

******ESTIMATE REALIZED VOLATILITY*****
use "$jfqa_rep/CRSP_Daily_Index.dta", clear

qui{
keep sprtrn spindx year month day 

drop if missing(sprtrn)

gen monthyear=ym(year, month)

*number of days
egen count_days=count(day), by(monthyear)

*annualize stdev
gen r_2=sprtrn^2
egen sum_r_2=sum(r_2), by(monthyear)
gen sp500_rv=sqrt((252/count_days)*sum_r_2)

keep year month monthyear sp500_rv

duplicates drop

tsset monthyear

keep sp500_rv year month monthyear
}

save "$jfqa_rep/T7_SP500_RV.dta", replace


***********CALCULATE MONTHLY S&P CONVEXITY*************
est clear

use "$jfqa_rep/CRSP_Daily_Index.dta", clear

qui{
drop if missing(spindx) 

sort year month day

gen monthyear=ym(year, month)

*assign first and last observation of month
gen prc_first=spindx if month~=month[_n-1]
egen min_prc=min(prc_first), by(year month)
replace prc_first=min_prc if missing(prc_first)
drop min_prc

gen prc_last=spindx if month~=month[_n+1]
egen min_prc=min(prc_last), by(year month)
replace prc_last=min_prc if missing(prc_last)
drop min_prc

sort year month
egen avgprc=mean(spindx), by(year month)
egen stdprc=sd(spindx), by(year month)
gen convexity=((prc_first+prc_last)/2-avgprc)/((prc_first+prc_last)/2)

drop if monthyear==monthyear[_n+1]

keep month year convexity
}

save "$jfqa_rep/T7_SP_Convexity.dta", replace


*****ESTIMATE REGRESSIONS*****
import delimited "$jfqa_rep/Goyal_Excel_Monthly.csv", clear

qui{
rename index sp_index 
rename d12 sp_div
rename e12 sp_earn

drop if missing(sp_index)

destring sp_index, force replace

merge 1:1 year month using "$jfqa_rep/CRSP_Monthly_Index.dta"
keep if _merge==3
drop _merge

gen monthyear=ym(year, month)
tsset monthyear
sort monthyear

gen log_prem=(crsp_spvw-log(1+rf))*100 

merge 1:1 year month using "$jfqa_rep/T7_SP_Convexity.dta"
keep if _merge==3
drop _merge
sort monthyear

rm "$jfqa_rep/T7_SP_Convexity.dta"

order year month monthyear log_prem

*construct control variables
gen dp = log(sp_div/sp_index)
gen dy = log(sp_div/L.sp_index)
gen ep = log(sp_earn/sp_index)
gen de = log(sp_div/sp_earn)
replace svar=svar
replace bm=bm
replace ntis=ntis
*gen beta_prem=csp
replace tbl=tbl
replace lty=lty
replace ltr=ltr
gen tms=lty-tbl
gen dfy = aaa-baa
gen dfr=corpr-ltr
replace infl=infl

drop if year<1963
drop if year==1963 & month<6

*merge variables
merge m:1 year month using "$jfqa_rep/T7_Amihud_Detrend.dta"
drop if _merge==2
drop _merge

rm  "$jfqa_rep/T7_Amihud_Detrend.dta"

merge m:1 year month using "$jfqa_rep/T7_SP500_RV.dta"
drop if _merge==2
drop _merge

rm "$jfqa_rep/T7_SP500_RV.dta"

merge m:1 year month using "$jfqa_rep/BW_Sentiment.dta"
drop if _merge==2
drop _merge sent sent_orth pdnd ripo cefd indpro consdur consnon consserv recess employ cpi

replace nipo=nipo/100

*remove observations
sort monthyear
gen fut_log_prem=F.log_prem
drop if missing(fut_log_prem)

*gen convexity standard deviation
egen convex_sd=sd(convexity)
egen convex_mean=mean(convexity)

*illiquidity
sort illiquid_detrend
gen illiquid_rank=.
replace illiquid_rank=1 if _n<_N/4 
replace illiquid_rank=1 if _n>=_N/4 & _n<_N/4*2 
replace illiquid_rank=2 if _n>=_N/4*2 & _n<_N/4*3 
replace illiquid_rank=2 if _n>=_N/4*3 

forval i=1/2 {
gen illiquid_d`i'=0
replace illiquid_d`i'=1 if illiquid_rank==`i'
gen convex_illiquid`i'=convexity*illiquid_d`i'
gen convex_illiquid_std`i'=(convex_illiquid`i'-convex_mean)/convex_sd
}

*volatility
sort sp500_rv
gen rv_rank=.
replace rv_rank=1 if _n<_N/4 
replace rv_rank=1 if _n>=_N/4 & _n<_N/4*2 
replace rv_rank=2 if _n>=_N/4*2 & _n<_N/4*3 
replace rv_rank=2 if _n>=_N/4*3 

forval i=1/2 {
gen rv_d`i'=0
replace rv_d`i'=1 if rv_rank==`i'
gen convex_rvd`i'=convexity*rv_d`i'
gen convex_rvd_std`i'=(convex_rvd`i'-convex_mean)/convex_sd
}

*nipo
sort nipo
gen nipo_rank=.
replace nipo_rank=1 if _n<_N/4 
replace nipo_rank=1 if _n>=_N/4 & _n<_N/4*2 
replace nipo_rank=2 if _n>=_N/4*2 & _n<_N/4*3 
replace nipo_rank=2 if _n>=_N/4*3 

forval i=1/2 {
gen nipo_d`i'=0
replace nipo_d`i'=1 if nipo_rank==`i'
gen convex_nipo`i'=convexity*nipo_d`i'
gen convex_nipo_std`i'=(convex_nipo`i'-convex_mean)/convex_sd
}

*equity share
sort s
gen s_rank=.
replace s_rank=1 if _n<_N/4 
replace s_rank=1 if _n>=_N/4 & _n<_N/4*2 
replace s_rank=2 if _n>=_N/4*2 & _n<_N/4*3 
replace s_rank=2 if _n>=_N/4*3 

forval i=1/2 {
gen s_d`i'=0
replace s_d`i'=1 if s_rank==`i'
gen convex_s`i'=convexity*s_d`i'
gen convex_s_std`i'=(convex_s`i'-convex_mean)/convex_sd
}

sort monthyear
qui{ 
*coefficients/t-stats
eststo: newey fut_log_prem convex_illiquid_std1 convex_illiquid_std2 , lag(6)
eststo: newey fut_log_prem convex_rvd_std1 convex_rvd_std2 , lag(6)

eststo: newey fut_log_prem convex_nipo_std1 convex_nipo_std2, lag(6)
eststo: newey fut_log_prem convex_s_std1 convex_s_std2, lag(6)

}
}

esttab using "$jfqa_rep/Table_7.csv", nostar replace b(2)

*R-squared
reg fut_log_prem convex_illiquid_std1 convex_illiquid_std2
reg fut_log_prem convex_rvd_std1 convex_rvd_std2 

reg fut_log_prem convex_nipo_std1 convex_nipo_std2
reg fut_log_prem convex_s_std1 convex_s_std2
